how to join and map using prisma client

My case

AcademyCoach와 User 사이의 릴레이션을 사용하되, userModel이라는 기본으로 지정된 필드가 아닌 userInfo라는 임의의 필드에 User 엔티티를 매핑하기 위해 사용한다.

  async listAcademyCoach(
    academyId: string,
  ): Promise<AcademyCoachDtoResponse[]> {
    return await this.prisma.academyCoach
      .findMany({
        include: {
          userModel: true, // JOIN and select related user entity into userModel
        },
        where: {
          academy: academyId,
        },
      })
      .then((dtos) =>
        dtos.map((dto): AcademyCoachDtoResponse => {
          const { userModel, ...rest } = dto;
          return {
            ...rest,
            userInfo: UserDto.from(userModel), // ← 바로 여기에서 매핑이 이루어진다.
          };
        }),
      );
  }

아래는 Prisma Schema

model User {
  id           String       @id @default(auto()) @map("_id") @db.ObjectId
  name         String

  createdTime DateTime  @default(now()) @db.Date
  updatedTime DateTime  @updatedAt @db.Date
  deletedTime DateTime? @db.Date

  AcademyCoach            AcademyCoach[]
  tickets                 Ticket[]

  @@map("usermodels")
}

model AcademyCoach {
  id          String @id @default(auto()) @map("_id") @db.ObjectId
  academy     String @db.ObjectId
  user        String @db.ObjectId

  createdTime DateTime  @default(now()) @db.Date
  updatedTime DateTime  @updatedAt @db.Date
  deletedTime DateTime? @db.Date
  
  userModel     User   @relation(fields: [user], references: [id])
  academyModel  Academy @relation(fields: [academy], references: [id])

  @@map("academycoachmodels")
}

GPT 설전

is there any way of mapping joined property in prisma? In TypeORM there is a method such as leftJoinAndMapOne can achieve my case.

Yes, Prisma does not have a direct equivalent to TypeORM's leftJoinAndMapOne, but you can achieve similar functionality using relations and computed fields.

Solution 1: Using include or select

If the relation is defined in your schema, you can use include or select to fetch the related entity.

Example:

model User {
  id    Int    @id @default(autoincrement())
  name  String
  profile Profile? @relation(fields: [profileId], references: [id])
  profileId Int?
}

model Profile {
  id    Int    @id @default(autoincrement())
  bio   String
  user  User?
}

To fetch User with the Profile included:

const users = await prisma.user.findMany({
  include: {
    profile: true,
  },
});

Solution 2: Using computed properties via a Service Layer

Since Prisma doesn't allow mapping a joined property directly like TypeORM's leftJoinAndMapOne, you can manually add the joined property in your service layer.

const usersWithMappedProfile = await prisma.user.findMany({
  include: {
    profile: true,
  },
});

const result = usersWithMappedProfile.map(user => ({
  ...user,
  profileBio: user.profile?.bio || null,
}));

This effectively adds a profileBio field like leftJoinAndMapOne does in TypeORM.

Solution 3: Raw SQL with prisma.$queryRaw

If you need a more complex join:

const usersWithProfile = await prisma.$queryRaw`
  SELECT u.id, u.name, p.bio AS profileBio
  FROM User u
  LEFT JOIN Profile p ON u.profileId = p.id;
`;

But this approach does not return a typed model like Prisma usually does.

Conclusion

While Prisma does not have an exact equivalent of leftJoinAndMapOne, the combination of:

  1. include / select for relations
  2. Transforming the result in your service layer
  3. Using raw SQL queries when necessary

achieves the same effect. The most idiomatic Prisma approach is to use include and process the mapping at the service layer.